﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Windows.Forms;
using DTO;
using System.Data;
using DAO;
namespace DAO
{
   public class DAOBaoCao
    {

        public List<HoaDonDTO> BaoCao_theoNgay(HoaDonDTO bc)
        {
            
            SqlConnection conn = DataProvider.sqlconn;
            string sql = "select day(giora)as ng,month(giora)as m,year(giora)as n,thanhtien from hoadon where ";
            sql = sql + "giovao between '" + bc.GioVao +"' and '" + bc.GioRa + "' and tinhtranghd=N'Đã tính tiền'";//Ok

           // MessageBox.Show(sql);
            SqlDataAdapter data = new SqlDataAdapter(sql, conn);
            DataTable dt = new DataTable();
            data.Fill(dt);
            List<HoaDonDTO> kq = new List<HoaDonDTO>();
            foreach (DataRow r in dt.Rows)
            {
                HoaDonDTO bc1 = new HoaDonDTO();
                bc1.Ngay = int.Parse(r["ng"].ToString());
                bc1.Thang = int.Parse(r["m"].ToString());
                bc1.Nam = int.Parse(r["n"].ToString());
                bc1.ThanhTien = float.Parse(r["thanhtien"].ToString());
                kq.Add(bc1);
            }
            conn.Close();
            return kq;
        }
        public List<HoaDonDTO> baocao_theothang(HoaDonDTO bc)
        {
            SqlConnection conn = DataProvider.sqlconn;
            string sql = "select month(giora)as m,year(giora)as n,sum(thanhtien) as tien from hoadon where ";
            sql = sql + "giovao between '" + bc.GioVao + "' and '" + bc.GioRa + "' and tinhtranghd=N'Đã tính tiền'";
            sql = sql+" group by month(giora),year(giora)";
            // MessageBox.Show(sql);
            SqlDataAdapter data = new SqlDataAdapter(sql, conn);
            DataTable dt = new DataTable();
            data.Fill(dt);
            List<HoaDonDTO> kq = new List<HoaDonDTO>();
            foreach (DataRow r in dt.Rows)
            {
                HoaDonDTO bc1 = new HoaDonDTO();
                bc1.Thang = int.Parse(r["m"].ToString());
                bc1.Nam = int.Parse(r["n"].ToString());
                bc1.ThanhTien = float.Parse(r["tien"].ToString());
                kq.Add(bc1);
            }
            conn.Close();
            return kq;
        }
        public List<HoaDonDTO> baocao_nam(HoaDonDTO bc)
        {
            SqlConnection conn = DataProvider.sqlconn;
            string sql = "select year(giora)as n,sum(thanhtien) as tien from hoadon where ";
            sql = sql + "giovao between '" + DateTime.Parse(bc.GioVao.ToString()).ToString("MM/dd/yyyy") + "' and '" + DateTime.Parse(bc.GioRa.ToString()).ToString("MM/dd/yyyy") + "' and tinhtranghd=N'Đã tính tiền'";
            sql = sql + " group by year(giora)";
            // MessageBox.Show(sql);
            SqlDataAdapter data = new SqlDataAdapter(sql, conn);
            DataTable dt = new DataTable();
            data.Fill(dt);
            List<HoaDonDTO> kq = new List<HoaDonDTO>();
            foreach (DataRow r in dt.Rows)
            {
                HoaDonDTO bc1 = new HoaDonDTO();
                bc1.Nam = int.Parse(r["n"].ToString());
                bc1.ThanhTien = float.Parse(r["tien"].ToString());
                kq.Add(bc1);
            }
            conn.Close();
            return kq;
        }
        public List<HoaDonDTO> BaoCaomon_theoNgay(HoaDonDTO bc,int gt)
        {

            SqlConnection conn = DataProvider.sqlconn;
            string sql = "select day(hd.giora)as ng,month(hd.giora)as m,year(hd.giora)as n,ct.tongtien from  chitiethoadon ct,monan ma,hoadon hd where ";
            sql = sql + "ct.mamonan=ma.mamonan and hd.mahd=ct.mahd and ( ";
            sql = sql + "giovao between '" + bc.GioVao + "' and '" + bc.GioRa + "' ) and tinhtranghd=N'Đã tính tiền' and ct.mamonan=" + gt;

            // MessageBox.Show(sql);
            SqlDataAdapter data = new SqlDataAdapter(sql, conn);
            DataTable dt = new DataTable();
            data.Fill(dt);
            List<HoaDonDTO> kq = new List<HoaDonDTO>();
            foreach (DataRow r in dt.Rows)
            {
                HoaDonDTO bc1 = new HoaDonDTO();
                bc1.Ngay = int.Parse(r["ng"].ToString());
                bc1.Thang = int.Parse(r["m"].ToString());
                bc1.Nam = int.Parse(r["n"].ToString());
                bc1.ThanhTien = float.Parse(r["tongtien"].ToString());
                kq.Add(bc1);
            }
            conn.Close();
            return kq;
        }

        public List<HoaDonDTO> BaoCaomon_theothang(HoaDonDTO bc, int gt)
        {

            SqlConnection conn = DataProvider.sqlconn;
            string sql = "select month(hd.giora)as m,year(hd.giora)as n,sum(ct.tongtien)as tien from  chitiethoadon ct,monan ma,hoadon hd where ";
            sql = sql + "ct.mamonan=ma.mamonan and hd.mahd=ct.mahd and ( ";
            sql = sql + "giovao between '" + bc.GioVao + "' and '" + bc.GioRa + "' ) and  tinhtranghd=N'Đã tính tiền' and ct.mamonan=" + gt;
            sql = sql + " group by month(giora),year(giora)";
            // MessageBox.Show(sql);
            SqlDataAdapter data = new SqlDataAdapter(sql, conn);
            DataTable dt = new DataTable();
            data.Fill(dt);
            List<HoaDonDTO> kq = new List<HoaDonDTO>();
            foreach (DataRow r in dt.Rows)
            {
                HoaDonDTO bc1 = new HoaDonDTO();
                bc1.Thang = int.Parse(r["m"].ToString());
                bc1.Nam = int.Parse(r["n"].ToString());
                bc1.ThanhTien = float.Parse(r["tien"].ToString());
                kq.Add(bc1);
            }
            conn.Close();
            return kq;
        }

        public List<HoaDonDTO> BaoCaomon_theonam(HoaDonDTO bc, int gt)
        {

            SqlConnection conn = DataProvider.sqlconn;
            string sql = "select year(hd.giora)as n,sum(ct.tongtien)as tien from  chitiethoadon ct,monan ma,hoadon hd where ";
            sql = sql + "ct.mamonan=ma.mamonan and hd.mahd=ct.mahd and tinhtranghd=N'Đã tính tiền' and ( ";
            sql = sql + "hd.giovao between '" + DateTime.Parse(bc.GioVao.ToString()).ToString("MM/dd/yyyy") + "' and '" + DateTime.Parse(bc.GioRa.ToString()).ToString("MM/dd/yyyy") + "' ) and ct.mamonan=" + gt;
            sql = sql + " group by year(hd.giora)";
            // MessageBox.Show(sql);
            SqlDataAdapter data = new SqlDataAdapter(sql, conn);
            DataTable dt = new DataTable();
            data.Fill(dt);
            List<HoaDonDTO> kq = new List<HoaDonDTO>();
            foreach (DataRow r in dt.Rows)
            {
                HoaDonDTO bc1 = new HoaDonDTO();
                bc1.Nam = int.Parse(r["n"].ToString());
                bc1.ThanhTien = float.Parse(r["tien"].ToString());
                kq.Add(bc1);
            }
            conn.Close();
            return kq;
        }

        public List<HoaDonDTO> BaoCaoBan_theoNgay(HoaDonDTO bc, string gt)
        {

            SqlConnection conn = DataProvider.sqlconn;
            string sql = "select day(hd.giora)as ng,month(hd.giora)as m,year(hd.giora)as n,sum(ct.tongtien)as tien from  chitiethoadon ct,hoadon hd where ";
            sql = sql + " hd.mahd=ct.mahd and tinhtranghd=N'Đã tính tiền' and ( ";
            sql = sql + "giovao between '" + bc.GioVao + "' and '" + bc.GioRa + "' ) and ct.maban=" + "'" + gt + "'";
            sql = sql + " group by day(hd.giora),month(hd.giora),year(hd.giora)";
            // MessageBox.Show(sql);
            SqlDataAdapter data = new SqlDataAdapter(sql, conn);
            DataTable dt = new DataTable();
            data.Fill(dt);
            List<HoaDonDTO> kq = new List<HoaDonDTO>();
            foreach (DataRow r in dt.Rows)
            {
                HoaDonDTO bc1 = new HoaDonDTO();
                bc1.Ngay = int.Parse(r["ng"].ToString());
                bc1.Thang = int.Parse(r["m"].ToString());
                bc1.Nam = int.Parse(r["n"].ToString());
                bc1.ThanhTien = float.Parse(r["tien"].ToString());
                kq.Add(bc1);
            }
            conn.Close();
            return kq;
        }

        public List<HoaDonDTO> BaoCaoBan_theothang(HoaDonDTO bc, string gt)
        {

            SqlConnection conn = DataProvider.sqlconn;
            string sql = "select month(hd.giora)as m,year(hd.giora)as n,sum(ct.tongtien)as tien from  chitiethoadon ct,hoadon hd where ";
            sql = sql + " hd.mahd=ct.mahd and tinhtranghd=N'Đã tính tiền' and ( ";
            sql = sql + "giovao between '" + bc.GioVao + "' and '" + bc.GioRa + "' ) and ct.maban=" + "'" + gt + "'";
            sql = sql + " group by month(hd.giora),year(hd.giora)";
            // MessageBox.Show(sql);
            SqlDataAdapter data = new SqlDataAdapter(sql, conn);
            DataTable dt = new DataTable();
            data.Fill(dt);
            List<HoaDonDTO> kq = new List<HoaDonDTO>();
            foreach (DataRow r in dt.Rows)
            {
                HoaDonDTO bc1 = new HoaDonDTO();
                bc1.Thang = int.Parse(r["m"].ToString());
                bc1.Nam = int.Parse(r["n"].ToString());
                bc1.ThanhTien = float.Parse(r["tien"].ToString());
                kq.Add(bc1);
            }
            conn.Close();
            return kq;
        }

        public List<HoaDonDTO> BaoCaoBan_theonam(HoaDonDTO bc, string gt)
        {

            SqlConnection conn = DataProvider.sqlconn;
            string sql = "select year(hd.giora)as n,sum(ct.tongtien)as tien from  chitiethoadon ct,hoadon hd where ";
            sql = sql + " hd.mahd=ct.mahd and tinhtranghd=N'Đã tính tiền' and ( ";
            sql = sql + "giovao between '" + DateTime.Parse(bc.GioVao.ToString()).ToString("MM/dd/yyyy") + "' and '" + DateTime.Parse(bc.GioRa.ToString()).ToString("MM/dd/yyyy") + "' ) and ct.maban=" + "'" + gt + "'";
            sql = sql + " group by year(hd.giora)";
            // MessageBox.Show(sql);
            SqlDataAdapter data = new SqlDataAdapter(sql, conn);
            DataTable dt = new DataTable();
            data.Fill(dt);
            List<HoaDonDTO> kq = new List<HoaDonDTO>();
            foreach (DataRow r in dt.Rows)
            {
                HoaDonDTO bc1 = new HoaDonDTO();

                bc1.Nam = int.Parse(r["n"].ToString());
                bc1.ThanhTien = float.Parse(r["tien"].ToString());
                kq.Add(bc1);
            }
            conn.Close();
            return kq;
        }

        public List<HoaDonDTO> BaoCaokhuvuc_theoNgay(HoaDonDTO bc, string gt)
        {

            SqlConnection conn = DataProvider.sqlconn;
            string sql = "select day(hd.giora)as ng,month(hd.giora)as m,year(hd.giora)as n,sum(ct.tongtien)as tien from  chitiethoadon ct,hoadon hd,ban b where ";
            sql = sql + " hd.mahd=ct.mahd and b.maban=ct.maban and tinhtranghd=N'Đã tính tiền' and ( ";
            sql = sql + "giovao between '" + bc.GioVao + "' and '" + bc.GioRa + "' ) and b.khuvuc=" + "'" + gt + "'";
            sql = sql + " group by day(hd.giora),month(hd.giora),year(hd.giora)";
            // MessageBox.Show(sql);
            SqlDataAdapter data = new SqlDataAdapter(sql, conn);
            DataTable dt = new DataTable();
            data.Fill(dt);
            List<HoaDonDTO> kq = new List<HoaDonDTO>();
            foreach (DataRow r in dt.Rows)
            {
                HoaDonDTO bc1 = new HoaDonDTO();
                bc1.Ngay = int.Parse(r["ng"].ToString());
                bc1.Thang = int.Parse(r["m"].ToString());
                bc1.Nam = int.Parse(r["n"].ToString());
                bc1.ThanhTien = float.Parse(r["tien"].ToString());
                kq.Add(bc1);
            }
            conn.Close();
            return kq;
        }

        public List<HoaDonDTO> BaoCaokhuvuc_theothang(HoaDonDTO bc, string gt)
        {

            SqlConnection conn = DataProvider.sqlconn;
            string sql = "select month(hd.giora)as m,year(hd.giora)as n,sum(ct.tongtien)as tien from  chitiethoadon ct,hoadon hd,ban b where ";
            sql = sql + " hd.mahd=ct.mahd and b.maban=ct.maban and hd.tinhtranghd=N'Đã tính tiền' and ( ";
            sql = sql + "giovao between '" + bc.GioVao + "' and '" + bc.GioRa + "' ) and b.khuvuc=" + "'" + gt + "'";
            sql = sql + " group by month(hd.giora),year(hd.giora)";
            // MessageBox.Show(sql);
            SqlDataAdapter data = new SqlDataAdapter(sql, conn);
            DataTable dt = new DataTable();
            data.Fill(dt);
            List<HoaDonDTO> kq = new List<HoaDonDTO>();
            foreach (DataRow r in dt.Rows)
            {
                HoaDonDTO bc1 = new HoaDonDTO();

                bc1.Thang = int.Parse(r["m"].ToString());
                bc1.Nam = int.Parse(r["n"].ToString());
                bc1.ThanhTien = float.Parse(r["tien"].ToString());
                kq.Add(bc1);
            }
            conn.Close();
            return kq;
        }

        public List<HoaDonDTO> BaoCaokhuvuc_theonam(HoaDonDTO bc, string gt)
        {

            SqlConnection conn = DataProvider.sqlconn;
            string sql = "select year(hd.giora)as n,sum(ct.tongtien)as tien from  chitiethoadon ct,hoadon hd,ban b where ";
            sql = sql + " hd.mahd=ct.mahd and b.maban=ct.maban and tinhtranghd=N'Đã tính tiền' and ( ";
            sql = sql + "giovao between '" + DateTime.Parse(bc.GioVao.ToString()).ToString("MM/dd/yyyy") + "' and '" + DateTime.Parse(bc.GioRa.ToString()).ToString("MM/dd/yyyy") + "' ) and b.khuvuc=" + "'" + gt + "'";
            sql = sql + " group by year(hd.giora)";
            // MessageBox.Show(sql);
            SqlDataAdapter data = new SqlDataAdapter(sql, conn);
            DataTable dt = new DataTable();
            data.Fill(dt);
            List<HoaDonDTO> kq = new List<HoaDonDTO>();
            foreach (DataRow r in dt.Rows)
            {
                HoaDonDTO bc1 = new HoaDonDTO();
                bc1.Nam = int.Parse(r["n"].ToString());
                bc1.ThanhTien = float.Parse(r["tien"].ToString());
                kq.Add(bc1);
            }
            conn.Close();
            return kq;
        }
    }
}
